package com.ckv.qcm.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.ckv.qcm.model.Trainee;

public class TraineeDAO extends DAO {

	public ArrayList<Long> getSessionIds(long idTrainee, java.util.Date date) throws SQLException {
		ArrayList<Long> ret=new ArrayList<Long>();
		
		SimpleDateFormat  formatter = new SimpleDateFormat("dd/MM/yyyy");
		
		try {
			ResultSet result = this.connection
					.createStatement()
					.executeQuery(
							"SELECT SESSIONS.idSession FROM SESSIONS "
									+ "INNER JOIN INSCRIPTION on INSCRIPTION.idSession=SESSIONS.idSession "
									+ "WHERE SESSIONS.DateDebut <= '" + formatter.format(date)
									+ "' AND SESSIONS.DateFin >= '" + formatter.format(date) + "'"
									+ " AND INSCRIPTION.idStagiaire="
									+ idTrainee);
			
			while (result.next())
			{
				ret.add(result.getLong("idSession"));
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw e;
		}
		return ret;

	}

	public Trainee find(String nom, String password) {
		Trainee trainee = null;

		try {
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT * FROM Stagiaires WHERE nom='" + nom
							+ "' AND password='" + password + "'");

			if (result.next()) {
				trainee = new Trainee();
				trainee.setId(result.getLong("idStagiaire"));
				trainee.setIdPromotion(result.getLong("idPromotion"));
				trainee.setName(result.getString("Nom"));
				trainee.setFirstName(result.getString("prenom"));
				trainee.setPassword(result.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return trainee;
	}

	public Trainee find(long id) {
		Trainee trainee = new Trainee();

		try {
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT * FROM Stagiaires WHERE idStagiaire=" + id);

			if (result.next()) {
				trainee.setId(result.getLong("idStagiaire"));
				trainee.setIdPromotion(result.getLong("idPromotion"));
				trainee.setName(result.getString("nom"));
				trainee.setFirstName(result.getString("prenom"));
				trainee.setPassword(result.getString("password"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return trainee;
	}

	public ArrayList<Trainee> findAll() {
		ArrayList<Trainee> trainees = new ArrayList<Trainee>();

		try {
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT idStagiaire FROM Stagiaires");

			while (result.next()) {
				trainees.add(Trainee.find(result.getLong("idStagiaire")));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return trainees;
	}

	public Trainee create(Trainee trainee) {
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {

			preparedStatement = this.connection
					.prepareStatement(
							"INSERT INTO Stagiaires (idStagiaire,idPromotion,nom,prenom,password) VALUES (?,?,?,?,?)",
							Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, trainee.getId());
			preparedStatement.setLong(2, trainee.getIdPromotion());
			preparedStatement.setString(3, trainee.getName());
			preparedStatement.setString(4, trainee.getFirstName());
			preparedStatement.setString(5, trainee.getPassword());

			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating section failed, no rows affected.");
			}

			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next()) {
				// section = this.find(generatedKeys.getLong(1));
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return trainee;

	}

}
